package com.ljqc.sbom.management.repository;


import com.alibaba.fastjson.JSONObject;
import com.ljqc.sbom.management.domain.SpdxVul;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

public interface SpdxVulRepository extends JpaRepository<SpdxVul, Integer> {


    @Query(nativeQuery = true, value = "SELECT LEVEL,count( LEVEL ) count  " +
            "FROM " +
            "(SELECT " +
            " vul_level LEVEL, " +
            " COUNT( id ) count  " +
            "FROM " +
            " spdx_vul  " +
            "WHERE " +
            " spdx_project_id = ?1  " +
            "GROUP BY " +
            " vul_id  " +
            " ) le  " +
            "GROUP BY " +
            "LEVEL")
    List<Map<Object, Object>> getAllProjectVulCount(Integer id);

    @Query(nativeQuery = true, value = "select vul_level,count(distinct(vul_id)) as 'count' " +
            "from spdx_vul " +
            "where spdx_project_id= ?1 " +
            "group by vul_level")
    List<JSONObject> getVulLevelCount(Integer spdxProjectId);


    @Query(nativeQuery = true, value = "select MAX(vul_level) level from spdx_vul WHERE spdx_project_id = ?1 ")
    Integer getMaxVulLeave(Integer projectId);

    @Query(nativeQuery = true, value = "select DISTINCT p.platform from spdx_vul v LEFT JOIN spdx_package p on v.spdx_package_id = p.id WHERE v.spdx_project_id = ?1 ")
    List<String> getVulPackages(Integer spdxProjectId);
    @Query(nativeQuery = true, value = "SELECT " +
        " *  " +
        "FROM " +
        " ( " +
        " SELECT " +
        "  *  " +
        " FROM " +
        "  ( " +
        "  SELECT " +
        "   repo_name as name, " +
        "   version, " +
        "   platform, " +
        "   packageId, " +
        "   cleanVersion, " +
        "   ljPackageId, " +
        "   solution, " +
        "   id, " +
        "   criNum, " +
        "   highNum, " +
        "   midNum, " +
        "   lowNum, " +
        "   unNum  " +
        "  FROM " +
        "   ( " +
        "   SELECT " +
        "    p.repo_name, " +
        "    p.version, " +
        "    p.platform, " +
        "    p.clean_version cleanVersion, " +
        "    p.move_to_solution solution, " +
        "    p.id packageId, " +
        "    p.lj_package_id ljPackageId , " +
        "    v.vul_level,  " +
        "    v.id, " +
        "   SUM( IF ( vul_level = 4, 1, 0 ) ) AS criNum, " +
        "   SUM( IF ( vul_level = 3, 1, 0 ) ) AS highNum, " +
        "   SUM( IF ( vul_level = 2, 1, 0 ) ) AS midNum, " +
        "   SUM( IF ( vul_level = 1, 1, 0 ) ) AS lowNum, " +
        "   SUM( IF ( vul_level = 0, 1, 0 ) ) AS unNum  " +
        "   FROM " +
        "    spdx_vul v " +
        "    LEFT JOIN spdx_package p ON v.spdx_package_id = p.id  " +
        "   WHERE " +
        "    v.spdx_project_id = ?5  " +
        "    AND v.lj_vul_score <= ?3  " +
        "    AND v.lj_vul_score >= ?4  " +
        "    and if( coalesce(?6 ,null ) is null,1=1,lower(p.repo_name) like lower(CONCAT( '%',?6, '%' ))) " +
            "   GROUP BY " +
            "    p.id  " +
            "   ) a   GROUP BY  repo_name,version,platform " +
        "  ) a  " +
        " WHERE " +
        "  ( criNum > 0 OR highNum > 0 OR midNum > 0 OR lowNum > 0 OR unNum > 0 )  " +
        " ) a " +
        " where  " +
        "               if(coalesce(?1, null) is null, 1 = 1, if(?1 = 0, (a.unNum > 0 and a.lowNum = 0 and a.midNum = 0 and a.highNum =0 and a.criNum = 0),   " +
        "                 if(?1 = 1, (a.lowNum > 0 and a.midNum = 0 and a.highNum =0 and a.criNum = 0) ,   " +
        "                 if(?1 = 2, (a.midNum > 0 and a.highNum =0 and a.criNum = 0) ,   " +
        "                 if(?1 = 3, (a.highNum > 0 and a.criNum = 0),  " +
        "         a.criNum > 0)))))   " +
        "              and if(coalesce(?2, null) is null, 1 = 1, a.platform = ?2)   " +
        "              order by a.criNum desc, a.highNum desc, a.lowNum desc, a.midNum desc, a.unNum desc ,a.id desc ", 
        countQuery = "SELECT " +
        " count(*) " +
        "FROM " +
        " ( " +
        " SELECT " +
        "  *  " +
        " FROM " +
        "  ( " +
        "  SELECT " +
        "   repo_name name, " +
        "   version, " +
        "   platform, " +
        "   packageId, " +
        "   cleanVersion, " +
        "   ljPackageId, " +
        "   solution, " +
        "   id, " +
        "   criNum, " +
        "   highNum, " +
        "   midNum, " +
        "   lowNum, " +
        "   unNum  " +
        "  FROM " +
        "   ( " +
        "   SELECT " +
        "    p.repo_name, " +
        "    p.version, " +
        "    p.platform, " +
        "    p.id packageId, " +
        "    p.lj_package_id ljPackageId , " +
        "    p.clean_version cleanVersion, " +
        "    p.move_to_solution solution, " +
        "    v.vul_level,  " +
        "    v.id, " +
        "   SUM( IF ( vul_level = 4, 1, 0 ) ) AS criNum, " +
        "   SUM( IF ( vul_level = 3, 1, 0 ) ) AS highNum, " +
        "   SUM( IF ( vul_level = 2, 1, 0 ) ) AS midNum, " +
        "   SUM( IF ( vul_level = 1, 1, 0 ) ) AS lowNum, " +
        "   SUM( IF ( vul_level = 0, 1, 0 ) ) AS unNum  " +
        "   FROM " +
        "    spdx_vul v " +
        "    LEFT JOIN spdx_package p ON v.spdx_package_id = p.id  " +
        "   WHERE " +
        "    v.spdx_project_id = ?5  " +
        "    AND v.lj_vul_score <= ?3  " +
        "    AND v.lj_vul_score >= ?4  " +
        "  and if( coalesce(?6 ,null ) is null,1=1,lower(p.repo_name) like lower(CONCAT( '%',?6, '%' ))) " +
                "   GROUP BY " +
        "    p.id  " +
        "   ) a   GROUP BY  repo_name,version,platform " +
        "  ) a  " +
        " WHERE " +
        "  ( criNum > 0 OR highNum > 0 OR midNum > 0 OR lowNum > 0 OR unNum > 0 )  " +
        " ) a " +
        " where  " +
        "               if(coalesce(?1, null) is null, 1 = 1, if(?1 = 0, (a.unNum > 0 and a.lowNum = 0 and a.midNum = 0 and a.highNum =0 and a.criNum = 0),   " +
        "                 if(?1 = 1, (a.lowNum > 0 and a.midNum = 0 and a.highNum =0 and a.criNum = 0) ,   " +
        "                 if(?1 = 2, (a.midNum > 0 and a.highNum =0 and a.criNum = 0) ,   " +
        "                 if(?1 = 3, (a.highNum > 0 and a.criNum = 0),  " +
        "         a.criNum > 0)))))   " +
        "              and if(coalesce(?2, null) is null, 1 = 1, a.platform = ?2)   " +
        "              order by a.criNum desc, a.highNum desc, a.lowNum desc, a.midNum desc, a.unNum desc ,a.id desc ")
    Page<JSONObject> getIngredientList(Integer vulLevel, String platform, Integer ljVulScoreMax, Integer ljVulScoreMin, Integer projectId, String repoName, Pageable page);


    @Query(nativeQuery = true, value = "SELECT " +
            " v.vul_title, " +
            " v.vul_id, " +
            " v.vul_published, " +
            " v.cve_id, " +
            " v.cwes, " +
            " v.vul_level, " +
            " v.cvss_score AS cvss_score, " +
            " v.exposure_level, " +
            " v.solve, " +
            " v.lj_vul_score  " +
            "FROM " +
            " spdx_vul v " +
            "WHERE " +
            " if(coalesce(?2,null) is null,1=1, v.spdx_package_id = ?2 )" +
            " AND v.spdx_project_id = ?1 " +
            " AND v.lj_vul_score <= ?3 AND v.lj_vul_score >= ?4 " +
            "GROUP BY " +
            " v.vul_id, " +
            " v.vul_level ",
    countQuery = "SELECT " +
            " count( * ) " +
            " FROM spdx_vul v " +
            "WHERE " +
            " if(coalesce(?2,null) is null,1=1, v.spdx_package_id = ?2 )" +
            " AND v.spdx_project_id = ?1 " +
            " AND v.lj_vul_score <= ?3 AND v.lj_vul_score >= ?4 " +
            "GROUP BY " +
            " v.vul_id, " +
            " v.vul_level ")
    Page<JSONObject> getVulDetail(Integer spdxProjectId, Integer spdxPackageId, Integer ljVulScoreMax, Integer ljVulScoreMin, Pageable page);


    @Query(nativeQuery = true, value = "SELECT " +
            " vul_id vulId, " +
            " vul_title vulTitle, " +
            " cve_id cveId, " +
            " cwes, " +
            " cvss_score cvssScore, " +
            " vul_level vulLevel, " +
            " lj_vul_score ljVulScore, " +
            " exposure_level exposureLevel, " +
            " count( 1 ) vulCount " +
            " FROM " +
            " spdx_vul  " +
            " WHERE " +
            " spdx_project_id = ?1  " +
            " and if(coalesce(?2,null) is null,1=1, (vul_id like CONCAT( '%',?2, '%' ) or cve_id like CONCAT( '%',?2, '%' ) or cwes  like CONCAT( '%',?2, '%' ))) " +
            " and if(coalesce(?3,null) is null,1=1, vul_level in (?3)) " +
            " and (lj_vul_score + 0) <= ?4 " +
            " and (lj_vul_score + 0) >= ?5 " +
            " GROUP BY " +
            " vul_id, " +
            " vul_level",
    countQuery = "SELECT " +
            " count( * ) " +
            " FROM " +
            " spdx_vul  " +
            " WHERE " +
            " spdx_project_id = ?1  " +
            " and if(coalesce(?2,null) is null,1=1, (vul_id like CONCAT( '%',?2, '%' ) or cve_id like CONCAT( '%',?2, '%' ) or cwes  like CONCAT( '%',?2, '%' ))) " +
            " and if(coalesce(?3,null) is null,1=1, vul_level in (?3)) " +
            " and (lj_vul_score + 0) <= ?4 " +
            " and (lj_vul_score + 0) >= ?5 " +
            " GROUP BY " +
            " vul_id, " +
            " vul_level")
    Page<JSONObject> getVulList(Integer spdxProjectId, String vulId, List<Integer> vulLevels, Integer ljVulScoreMax, Integer ljVulScoreMin, Pageable page);


    @Transactional
    void deleteAllBySpdxProjectId(Integer projectId);
}
